import utils.JdbcUtil;
import utils.SqlFactory;
import utils.annotations.Aggregated;
import utils.annotations.Multiple;
import utils.annotations.Update;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;


//自定义 mapper 进行CRUD
interface MyInterface {
    @Update(sql = "insert into student(id,name,age,teacher_id,info) values(?,?,?,?,?);")
    void add(int id, String name, int age, int teacherId, String info);

    @Update(sql = "delete from student where id = ?;")
    void deleteById(int id);

    @Update(sql = "update student set info = ? where id = ?;")
    void changeInfoById(String msg, int id);

    @Aggregated(sql = "select info from student where id = ?;")
    String getInfoById(int id);

    @Aggregated(sql = """
            select s.teacher_id = (select t.id from teacher t where t.name = ?) as ans
            from student s where s.name = ?;
             """)
    boolean judgeRelationship(String teacherName, String studentName);
}

final class DbInfo {
    public static final String DRIVER = "mysql.cj.jdbc.Driver";
    public static final String USER = System.getenv("MYSQL_USER");
    public static final String PASSWORD = System.getenv("MYSQL_PASSWORD");
    public static final String URL = String.format("jdbc:mysql://%s:%s",System.getenv("MYSQL_HOST"),System.getenv("MYSQL_PORT"));
    public static final String[] otherConfigs = new String[]{
            "drop database if exists _test_; ",
            "create database _test_;",
            "use _test_;",
            """
                create table student(
                    id int primary key,
                    name varchar(255),
                    age int,
                    teacher_id int not null,
                    info text
                    );
                """,
            """
                create table teacher(
                    id int primary key,
                    name varchar(255)
                );   
                """};
}


public class Main {
    // 可以在任何地方使用自定义接口完成 crud
    private interface InnerMapper {
        @Update(sql = "insert into teacher(id,name) values(?,?)")
        void addTeacher(int id, String name);
    }

    private static final InnerMapper mapper2 = JdbcUtil.getMapper(Main.class, InnerMapper.class);

    private static final MyInterface mapper = JdbcUtil.getMapper(Main.class, MyInterface.class);


    //多行返回值： 只能在注解在普通方法上
    @Multiple(sql = """
                select s.name from student s join teacher t
                    on t.id = s.teacher_id
                    where t.name = ?
            """)
    public static String[] getStudentsByTeacherName(String teacherName) throws NoSuchMethodException {
        return SqlFactory.query(
                Main.class.getMethod("getStudentsByTeacherName", String.class),
                r -> r.getString(1), //收集函数
                r -> r.toArray(String[]::new),  //映射函数
                teacherName
        );
    }

    //手动传入sql,处理多列返回值
    public static Map<String, String> getStudentsWithTeachers() throws SQLException {
        String sql = "select s.name, t.name from student s join teacher t on s.teacher_id = t.id";
        return SqlFactory.handleSingleResult(
                SqlFactory.handleQuery(sql, (Object) null),
                resultSet -> {
                    var map = new HashMap<String, String>();
                    do {
                        map.put(resultSet.getString(1)+"'s teacher ", resultSet.getString(2));
                    } while (resultSet.next());
                    return map;
                }
        );
    }

    public static void main(String[] args) throws NoSuchMethodException, SQLException {
        JdbcUtil.connect(DbInfo.URL, DbInfo.USER, DbInfo.PASSWORD, DbInfo.otherConfigs);
        mapper2.addTeacher(1, "TA1");
        mapper2.addTeacher(2, "TA2");
        mapper.add(0, "tom", 15, 1, "tom from teacher 1");
        mapper.add(1, "amy", 16, 1, "amy from teacher 1");
        mapper.add(2, "alex", 17, 2, "alex from teacher 2");
        mapper.add(3, "smart", 18, 1, "smart from teacher 1");
        System.out.println("info 更改前："+mapper.getInfoById(1));
        mapper.changeInfoById("(changed) amy from teacher 1", 1);
        System.out.println("info 更改后："+mapper.getInfoById(1));
        System.out.println("获取TA1的所有学生："+Arrays.toString(getStudentsByTeacherName("TA1")));
        System.out.println("获取所有学生对应的老师："+getStudentsWithTeachers());
        SqlFactory.handleUpdate("drop table student");
        SqlFactory.handleUpdate("drop table teacher");
    }
}